package queries;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import entitys.Customer;

public class WeeklyAnalysisQuery {
/**
 * Execute query - get all the calculations needed from DB to produce report.
 * @param conn
 * @return ArrayList<String>
 */
	public static ArrayList<Customer> exe(Connection conn) {
		ArrayList<String> custWithPrice = new ArrayList<String>();
		ArrayList<String> custWithSold= new ArrayList<String>();
		ArrayList<String> custWithAmount= new ArrayList<String>();
		ArrayList<String> custWithProdCount= new ArrayList<String>();
		ArrayList<String> custWithAllProdCount= new ArrayList<String>();
		ArrayList<Integer> seg = new ArrayList<Integer>();
		ArrayList<Integer> interest = new ArrayList<Integer>();
		ArrayList<Customer> c= new ArrayList<Customer>();
		ResultSet res = null; 
		Statement stmt;
			try {
				stmt = conn.createStatement();
				res = stmt.executeQuery("SELECT s.custID ,sum(p.price) "
						+ "FROM sales AS s , product AS p "
						+ "WHERE s.sold=1 AND s.productID=p.id "
						+ "GROUP BY s.custID;"); 
				while(res.next())
				{
					custWithPrice.add(new String(res.getString(1)+" "+res.getString(2)));
				}
				res = stmt.executeQuery("SELECT sales.custID ,COUNT(DISTINCT custID) FROM ccrm.sales,ccrm.product where sales.productID=product.id AND sales.sold=1 GROUP BY sales.custID;");
				while(res.next())
				{
					custWithSold.add(new String(res.getString(1)+" "+res.getString(2)));
				}
				res = stmt.executeQuery("SELECT SALES.custID ,COUNT(custID) FROM ccrm.sales GROUP BY custID ;");
				while(res.next())
				{
					custWithAmount.add(new String(res.getString(1)+" "+res.getString(2)));
				}
				res = stmt.executeQuery("SELECT sales.custID ,sales.productID,COUNT(productID) FROM ccrm.sales where sales.sold=1 GROUP BY sales.custID,sales.productID;");
				while(res.next())
				{
					custWithProdCount.add(new String(res.getString(1)+" "+res.getString(2)+" "+res.getString(3)));
				}
				res = stmt.executeQuery("SELECT sales.custID ,sales.productID,COUNT(productID) FROM ccrm.sales GROUP BY sales.custID,sales.productID;");
				while(res.next())
				{
					custWithAllProdCount.add(new String(res.getString(1)+" "+res.getString(2)+" "+res.getString(3)));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			// (sold/amount)*(1-sold/price)*10
			float sold;
			float amount;
			float price;
			String custID;
			String prodID;
			int j=0;
			///////////////////////////////////////////////////////////////////////
			for(int i=0;i<custWithSold.size();i++)
			{
				while(!custWithSold.get(i).substring(0, custWithSold.get(i).indexOf(" ")).equals(custWithAmount.get(j).substring(0, custWithAmount.get(j).indexOf(" ")))&& j<custWithAmount.size())
				{
					seg.add(0);
					j++;
				}
				
				sold = Float.parseFloat(custWithSold.get(i).substring(custWithSold.get(i).indexOf(" ")));
				amount = Float.parseFloat(custWithAmount.get(j).substring(custWithAmount.get(j).indexOf(" ")));
				price = Float.parseFloat(custWithPrice.get(i).substring(custWithPrice.get(i).indexOf(" ")));
				seg.add((int) ((sold/amount)*(1-sold/price)*10));
				//interest.add((int)((sold/amount)*10));
				j++;
			}
			
			while(j<custWithAmount.size())
			{
				//interest.add(0);
				seg.add(0);
				j++;
			}
			System.out.println(custWithProdCount);
			System.out.println(custWithAllProdCount);
			///////////////////////////////////////////////////////////////////////////////
			
			String prodSold[] = new String[3];
			String prodTot[] = new String[3];
			j=0;
			for(int i=0;i<custWithProdCount.size();i++)
			{
				while(!custWithProdCount.get(i).substring(0, custWithProdCount.get(i).indexOf(" ")).equals(custWithAllProdCount.get(j).substring(0, custWithAllProdCount.get(j).indexOf(" ")))&& j<custWithAllProdCount.size())
				{
					interest.add(0);
					j++;
				}
				prodSold = custWithProdCount.get(i).split(" ");
				prodTot = custWithAllProdCount.get(j).split(" ");
				sold = Float.parseFloat(prodSold[2]);
				amount = Float.parseFloat(prodTot[2]);
				interest.add((int)((sold/amount)*10));
				j++;
			}
			
			while(j<custWithAllProdCount.size())
			{
				interest.add(0);
				j++;
			}
			System.out.println(interest);
			///////////////////////////////////////////////////////////////////////////////
			int i=0;
			for(Integer s:seg)
			{
				System.out.println(custWithAmount.get(i).substring(0, custWithAmount.get(i).indexOf(" "))+" "+s);
				i++;
			}
			try {
				stmt = conn.createStatement();
				
				for(i=0;i<seg.size();i++)
				{
					stmt.executeUpdate("UPDATE `ccrm`.`customer` SET `sagnificance`='"+seg.get(i).toString()+"' WHERE `id`='"+custWithAmount.get(i).substring(0, custWithAmount.get(i).indexOf(" "))+"';");
					c.add(new Customer(custWithAmount.get(i).substring(0, custWithAmount.get(i).indexOf(" ")), seg.get(i),1));
				}
				System.out.println("------------------");
				for(i=0;i<custWithAllProdCount.size();i++)
				{
					custID = custWithAllProdCount.get(i).substring(0, custWithAllProdCount.get(i).indexOf(" "));
					prodID = custWithAllProdCount.get(i).substring(custWithAllProdCount.get(i).indexOf(" ")+1, custWithAllProdCount.get(i).length());
					res = stmt.executeQuery("SELECT custID FROM ccrm.custprodinterest WHERE custID="+custID+" AND prodID="+prodID.substring(0, prodID.indexOf(" "))+";");
					if(res.next())
					{
						stmt.executeUpdate("UPDATE `ccrm`.`custprodinterest` SET `interestLevel`='"+interest.get(i)+"' WHERE `custID`='"+custID+"' and`prodID`='"+prodID.substring(0, prodID.indexOf(" "))+"';");
					}
					else
					{
						stmt.executeUpdate("INSERT INTO `ccrm`.`custprodinterest` (`custID`, `prodID`, `interestLevel`) VALUES ('"+custID+"', '"+prodID.substring(0, prodID.indexOf(" "))+"', '"+interest.get(i)+"');");				
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		
		return c;
	}

}
